package com.japp.manager.service;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.Datum;
import oracle.sql.STRUCT;

import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.support.SqlSessionDaoSupport;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.support.nativejdbc.C3P0NativeJdbcExtractor;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.japp.base.BaseService;
import com.japp.manager.bean.QuantizaTionBean;
import com.japp.manager.bean.RealityQtBean;
import com.japp.manager.bean.TopBean;
import com.japp.manager.mapper.QuantizaTionMapper;
import com.japp.tools.common.Common;
import com.japp.tools.common.JappLog;
/**
 * 量化考核
 * @author Jion
 *
 */
@Service
public class RealityQtService extends SqlSessionDaoSupport implements BaseService{

	@Autowired
	private SqlSessionTemplate template;
	@Autowired
	private QuantizaTionMapper quantizaTionMapper;

	@Transactional(readOnly = true)
	public Map<String, Object> findRealityQt(RealityQtBean q) {
		Map<String, Object> map = new HashMap<String, Object>();
		if ("2".equals(q.getQ_date_type())) {
			q.setQ_cdate(String.valueOf(Common.getThisDateWEEK(q.getQ_date())));
		}
		String date = q.getQ_date();
		String cdate = q.getQ_cdate();
		String date_type = q.getQ_date_type();
		String power = q.getQ_power();
		String dpid = q.getQ_dpid();
		String ucode = q.getQ_ucode();
		if ("1".equals(power)) {
			dpid = "";
			ucode = "";
		} else if ("2".equals(power)) {
			ucode = "";
		}
		power = "";
		Connection conn = null;
		CallableStatement call = null;
		ResultSet rs = null;
		try {
			conn = template.getSqlSessionFactory().openSession().getConnection();
			/******正常数据开始******/
			JappLog.log.info("正常数据: 量化数据开始");
			//经营数据
			call = conn.prepareCall("{CALL PRO_REALITY_QT(?,?,?,?,?,?)}");
			call.setString(1, date_type);
			call.setString(2, date);
			call.setString(3, cdate);
			call.setString(4, dpid);
			call.setString(5, ucode);
			call.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
			call.execute();
			rs = (ResultSet) call.getObject(6);
			if (rs.next()) {
				q = (RealityQtBean) Common.getObjView(q, rs);
			}
			map.put("r",q);
			/******年度统计开始******/
			JappLog.log.info("年度统计: 量化数据开始");
			q = new RealityQtBean();
			q.setQ_date(date);
			q.setQ_cdate(cdate);
			q.setQ_date_type(date_type);
			q.setQ_dpid(dpid);
			q.setQ_power(power);
			q.setQ_ucode(ucode);
			call.close();
			call = null;
			call = conn.prepareCall("{CALL PRO_REALITY_QT(?,?,?,?,?,?)}");
			call.setString(1, date_type);
			call.setString(2, date);
			call.setString(3, "ALL");
			call.setString(4, dpid);
			call.setString(5, ucode);
			call.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
			call.execute();
			rs = (ResultSet) call.getObject(6);
			if (rs.next()) {
				q = (RealityQtBean) Common.getObjView(q, rs);
			}
			map.put("c",q);
			/******年度统计结束******/
			/******计划开始******/
			JappLog.log.info("计划数据: 量化数据开始");
			QuantizaTionBean qb = new QuantizaTionBean();
			qb.setQ_type(date_type);
			qb.setQ_submit_date(date);
			qb.setQ_submit_cdate(cdate);
			qb.setQ_submit_ucode(ucode);
			qb.setQ_submit_dpid(dpid);
			qb.setQ_power(power);
			for (int i = Integer.valueOf(date_type); i < 7; i++) {
				qb.setQ_type(String.valueOf(i));
				if(i==4){
					qb.setQ_submit_cdate(Common.getThisDateJdStart(cdate,date));
				}else if(i==5){
					qb.setQ_submit_cdate(Common.getThisDateYear1(date));
				}
				map.put("en_"+i, quantizaTionMapper.findQuantizaTionByBean(qb));
			}
			/******计划结束******/
			/*****上期数查询开始*****/
			JappLog.log.info("正常上期数据: 量化数据开始");
			q = new RealityQtBean();
			q.setQ_date(date);
			q.setQ_cdate(cdate);
			q.setQ_date_type(date_type);
			q.setQ_dpid(dpid);
			q.setQ_power(power);
			q.setQ_ucode(ucode);
			//判断开始
			if("1".equals(date_type)){
				date = Common.getThisDateYMD(date, "yyyy-MM-dd", "D", -1);
			}else if("2".equals(date_type)){
				date = Common.getThisDateYMD(date, "yyyy-MM-dd", "IW", -1);
				cdate = String.valueOf(Common.getThisDateWEEK(date));
			}else if("3".equals(date_type)){
				date = Common.getThisDateYMD(date, "yyyy-MM", "M", -1);
			}else if("4".equals(date_type)){
				if("1".equals(cdate)){
					cdate = "4";
					date = (date.length()==4?(date+"-01-01"):date);
					date = Common.getThisDateYMD(date, "yyyy-MM-dd", "Y", -1);
				}else{
					date = date.substring(0,4)+Common.getThisDateJdStart(cdate,date).substring(4,10);
					cdate = String.valueOf(Integer.valueOf(cdate)-1);
				}
			}else if("5".equals(date_type)){
				if("1".equals(cdate)){
					cdate = "2";
					date = Common.getThisDateYMD(date.substring(0,4), "yyyy", "Y", -1);
				}else{
					cdate = "1";
				}
			}else if("6".equals(date_type)){
				date = Common.getThisDateYMD(date.substring(0,4), "yyyy", "Y", -1);
			}
			//经营数据
			call.close();
			call = null;
			call = conn.prepareCall("{CALL PRO_REALITY_QT(?,?,?,?,?,?)}");
			call.setString(1, date_type);
			call.setString(2, date);
			call.setString(3, cdate);
			call.setString(4, dpid);
			call.setString(5, ucode);
			call.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
			call.execute();
			rs = (ResultSet) call.getObject(6);
			if (rs.next()) {
				q = (RealityQtBean) Common.getObjView(q, rs);
			}
			map.put("r-1", q);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return map;
	}
	
	/**
	 * 经营排行查询
	 * @param t
	 * @return
	 */
	public Map<String, Object> findRealityTop(TopBean t){
		Map<String, Object> map = new HashMap<String, Object>();
		if("2".equals(t.getDate_type())){
			t.setCdate(String.valueOf(Common.getThisDateWEEK(t.getDate())));
		}
		//t.setDate("2013-06-12");
		//t.setCdate("24");
		//t.setDate_type("2");
		String date_type = t.getDate_type();
		String date = t.getDate();
		String cdate = t.getCdate();
		//String top_type = "1";
		OracleConnection conn = null;
		Connection con = null;
		OracleCallableStatement call = null;
		try {
			con = template.getSqlSessionFactory().openSession().getConnection();
			C3P0NativeJdbcExtractor c3p0 = new C3P0NativeJdbcExtractor();
			conn = (OracleConnection)c3p0.getNativeConnection(con);
			call = (OracleCallableStatement)conn.prepareCall("{CALL PRO_REALITY_QTTOP(?,?,?,?)}");
			call.setString(1, date_type);
			call.setString(2, date);
			call.setString(3, cdate);
			call.registerOutParameter(4, OracleTypes.ARRAY,"EMP_ARRAY_OBJ");
			call.execute();
			ARRAY array = call.getARRAY(4);			//获取返回参数
			lt = getTopList(array,0);
			map.put("khqy", lt);
			lt = getTopList(array,1);
			map.put("jye", lt);
			lt = getTopList(array,2);
			map.put("sxfsr", lt);
			lt = getTopList(array,3);
			map.put("glfy", lt);
			lt = getTopList(array,4);
			map.put("yyfy", lt);
			lt = getTopList(array,5);
			map.put("yyly", lt);
			lt = getTopList(array,6);
			map.put("ywzdf", lt);
			lt = getTopList(array,7);
			map.put("clf", lt);
			lt = getTopList(array,8);
			map.put("clf9", lt);
			lt = getTopList(array,9);
			map.put("clf10", lt);
			lt = getTopList(array,10);
			map.put("clf11", lt);
			lt = getTopList(array,11);
			if(lt!=null&&lt.size()>0){
				for (TopBean ts: lt) {
					map.put(ts.getData_type(), ts.getType_sum());
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return map;
	}
	private TopBean top = new TopBean();
	private List<TopBean> lt = null;
	public List<TopBean> getTopList(ARRAY array,int index)throws Exception{
		lt = new ArrayList<>();
		array = (ARRAY)array.getOracleArray()[index];
		Datum[] dtm = array.getOracleArray();
		int len = dtm.length;
		if(len==0){
			return null;
		}
		Object[] obj = null;
		for (int i = 0; i < dtm.length; i++) {
		    STRUCT st = (STRUCT) dtm[i];		//需要否则乱码nls_charset12.jar  oracle11g orai18n.jar
		    if(st!=null){
		    	obj = st.getAttributes();
			    if(obj!=null&&obj.length!=0){
			    	if(obj[0]==null){
			    		continue;
			    	}
			    	top = new TopBean();
			    	top.setQ(obj[0].toString());
				    top.setData_type(obj[1].toString());
				    top.setDp_id(obj[2].toString());
				    top.setDp_name(obj[3].toString());
				    top.setUcode(obj[4].toString());
				    top.setUname(obj[5].toString());
				    top.setType_csum(Double.valueOf(obj[6].toString()));
				    top.setType_sum(Double.valueOf(obj[7].toString()));
				    lt.add(top);
			    }
		    }
		}
		return lt;
	}
	
}
